%macro get_deps;


	
	proc printto log = "&logdir/get_deps.txt" new;
	run;
	proc sort data = rlib.filing_status(where=(prim_tin_x~=.)) out = unique nodupkey;
		by prim_tin_x;
	run;

	data _null_;
		set unique;
		file "&tmpdir/tinlist" dlm=',';
		put prim_tin_x;
	run;


	proc sql noprint;
		[connection details redacted]
			create table deps as
			select * from connection to iq 
		(
		select distinct
			[redacted] as person_id
		into #t1
		from [redacted]
		where [redacted] = &base_yr 
			and [redacted] ~= 7
			and [redacted] = 1
		
		
		select distinct
			a.prim_tin_x,
			c.[redacted] as dep_ssn,
			b.[redacted] as dt,
			floor(d.[redacted]/10000) as dep_yob,
			(case when e.[redacted] is not null then 1 else 0 end) as student
		from #tinlist as a 
			inner join [redacted] as b 
				on(a.prim_tin_x = b.[redacted])
			inner join [redacted] as c 
				on(b.[redacted] = c.[redacted])
			inner join [redacted] as d 
				on(c.[redacted] = d.[redacted])
			left outer join #t1 as e
				on(b.[redacted] = e.person_id)
		where b.tax_yr = &base_yr-1
			and b.dep_type in("01","02","03","04","05","06","07")
		order by a.prim_tin_x,
			dep_ssn
		
	);
	disconnect from iq;
	quit;

	/* merge in the prim_ssn, sec_ssn */
	proc sql noprint;
	create table rlib.deps as
	select 
		a.dep_ssn,
		a.dt,
		a.dep_yob,
		a.student,
		b.prim_ssn,
		b.sec_ssn,
		b.prim_tin,
		b.sec_tin
	from deps as a
		inner join rlib.filing_status as b
			on(a.prim_tin_x = b.prim_tin_x);
	
	/*
	proc sort data = deps_merged nodupkey
		out = rlib.deps;
		by prim_tin sec_tin dep_ssn;
	run;
	*/
	

	proc printto;
	run;

%mend;